Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Using field lists when updating records

You can instruct the DataServer to use field lists when your application needs to update records. The DataServer uses the technique of optimistic updates to allow you to retrieve and update only the fields you need. ORACLE handles the record-locking when the update occurs. Activate this feature by specifying -Dsrv optimistic when you start the DataServer.

Note: Optimistic updates are not allowed for LONG, RAW, and LONG RAW columns.

Typically, an application has to obtain a record with an EXCLUSIVE-LOCK (either by explicitly specifying the EXCLUSIVE-LOCK or by a SHARE-LOCK that is upgraded to EXCLUSIVE-LOCK). Optimistic updates allow changes to be made to records that you retrieve NO-LOCK. Since field lists require that you obtain records with NO-LOCK, you can use field lists combined with optimistic updates to perform updates without retrieving the entire record. For example, the following code is acceptable if you specify the -Dsrv optimistic startup parameter:

FOR EACH customer FIELDS (cust_num name) NO-LOCK: UPDATE name.

The DataServer generates SQL similar to the following:

SELECT cust_num, name FROM sports.customer UPDATE sports.customer SET name=:x1 WHERE cust_num=:rid AND name=:o1

The bind variable :x1 represents the new value for the name column and :o1 supplies the old value. The clause, WHERE cust_num=:rid, specifies which row to update (in this example cust_num supports the Progress 4GL ROWID function). The name=:o1 portion of the WHERE clause prevents the UPDATE from taking place if another client has changed the name column while your client was holding it NO-LOCK.

The DataServer instructs ORACLE to compare the old value of name to its present value. If the values are the same (indicating that no one changed the record while your client held the record NO-LOCK), ORACLE updates the field. This feature enhances performance by reducing concurrency problems resulting from locks held for long periods and by reducing network traffic, as you can send only those fields you want to update.

Caution: Using optimistic locking can cause the application to appear to hang. If one user already has a record locked prior to a second user executing the above 4GL with optimistic locking, the second user’s application will appear to hang waiting for the first user to release the record. The standard table in use message is not displayed.